package com.production.CoursePlatform.mapper;

import com.production.CoursePlatform.dto.CourseWithUnreadCountDTO;
import com.production.CoursePlatform.entity.StudentNotice;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

@Mapper
public interface StudentNoticeMapper {
    @Select("SELECT COUNT(*) from student_notice WHERE cnID=#{cnID} and isRead=0")
    int getStudentNumberOfCtID(int cnID);

    @Update("UPDATE student_notice SET isRead =1 WHERE snID = #{snID}")
    int updateStudying(int snID);

    @Select("SELECT * FROM student_notice WHERE cnID = #{cnID} and isRead=0")
    List<StudentNotice> findUnreadInCtID(int cnID);

    @Select("SELECT " +
            "course.c_name, " +
            "COUNT(student_notice.snID) AS totalNum " +
            "FROM course " +
            "JOIN course_notice ON course.c_id = course_notice.ct_id " +
            "JOIN student_notice ON course_notice.cn_id = student_notice.cnID " +
            "WHERE student_notice.sID = #{sID} AND student_notice.isRead = '0' " +
            "GROUP BY course.c_name")
    List<CourseWithUnreadCountDTO> findCoursesWithUnreadNoticesBySID(int sID);

    @Select("SELECT SUM(CASE WHEN student_notice.isRead = '0' THEN 1 ELSE 0 END) AS totalUnreadMessages " +
            "FROM course " +
            "JOIN course_notice ON course.c_id = course_notice.ct_id " +
            "JOIN student_notice ON course_notice.cn_id = student_notice.cnID " +
            "WHERE student_notice.sID = #{sID}")
    int findTotalUnreadMessagesBySID(int sID);
}
